package com.soft.jdbc.student.dao;

import com.soft.jdbc.student.entity.Student;
import com.soft.jdbc.student.utils.C3P0Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Date;
import java.util.List;

/**
 * @author yzg
 * @desc TODO
 * @date 2025/6/6
 */
public class StudentDaoImpl implements StudentDao{
    @Override
    public int countStudents() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int count = 0;

        try {
            conn = C3P0Util.getConnection();
            String sql = "select count(*) from stus";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while(rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            C3P0Util.close(conn);
        }

        return count;
    }

    @Override
    public List<Student> getAllStudents() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();

        try {
            conn = C3P0Util.getConnection();
            String sql = "select * from stus";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Student student = new Student(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("grade"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getDate("birthday")
                );
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            C3P0Util.close(conn);
        }

        return students;
    }

    @Override
    public Student getStudentById(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Student student = null;

        try {
            conn = C3P0Util.getConnection();
            String sql = "select * from stus where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                student = new Student(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("grade"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getDate("birthday")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            C3P0Util.close(conn);
        }

        return student;
    }

    @Override
    public List<Student> getStudentsByName(String name) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();

        try {
            conn = C3P0Util.getConnection();
            String sql = "select * from stus where name like ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + name + "%");
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Student student = new Student(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("grade"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getDate("birthday")
                );
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            C3P0Util.close(conn);
        }

        return students;
    }

    @Override
    public boolean updateStudentBirthday(String id, Date birthday) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = C3P0Util.getConnection();
            String sql = "update stus set birthday = ? where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setDate(1, birthday);
            pstmt.setString(2, id);

            int row = pstmt.executeUpdate();
            success = row > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            C3P0Util.close(conn);
        }

        return success;
    }

    @Override
    public boolean deleteStudent(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = C3P0Util.getConnection();
            String sql = "delete from stus where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);

            int row = pstmt.executeUpdate();
            success = row > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            C3P0Util.close(conn);
        }

        return success;
    }

    @Override
    public boolean validLogin(String username, String password) {
        return "张无忌".equals(username) && "8888".equals(password);
    }
}
